﻿using ConfOrderDS;
using DevExpress.Utils;
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Controls;
using DevExpress.XtraEditors.Repository;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Base;
using DevExpress.XtraGrid.Views.Grid;
using Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace ConfExpert.Utility
{
    public class RepositoryManager
    {
        DateTime MxA, MxK, Mx;
        public RepositoryManager()
        {
            string info = "";

            MxA = MxK = Mx = new DateTime(2000, 1, 1);

            try
            {
                if (File.Exists(AppSession.sqlitefileDelete))
                {
                    //impl. @05.04.2015
                    File.Delete(AppSession.sqlitefile);
                    File.Delete(AppSession.sqlitefileDelete);
                }

                else
                {
                    //impl. @23.01.2015
                    string connstr = "Data Source=" + AppSession.sqlitefile;    // +"; PRAGMA synchronous=off; PRAGMA journal_mode = MEMORY";
                    if (File.Exists(AppSession.sqlitefile))
                        using (var con = new SQLiteConnection(connstr))
                        {
                            con.Open();

                            var sqlAdapter = new SQLiteDataAdapter("SELECT * FROM V_Kumas", con);
                            DataTable table = new DataTable();
                            sqlAdapter.Fill(table);
                            RepositoryManager.dsi.V_Kumas.Merge(table);
                            MxK = (DateTime)RepositoryManager.dsi.V_Kumas.Compute("MAX(ModifiedOn)", string.Empty);

                            sqlAdapter = new SQLiteDataAdapter("SELECT * FROM V_Aksesuar", con);
                            table = new DataTable();
                            sqlAdapter.Fill(table);
                            RepositoryManager.dsi.V_Aksesuar.Merge(table);
                            MxA = (DateTime)RepositoryManager.dsi.V_Aksesuar.Compute("MAX(ModifiedOn)", string.Empty);
                        }

                    Mx = MxK > MxA ? MxK : MxA;
                }


                info = "Zone:1. ";
                AppDS NewAppDS = RefreshData(Mx);

                info = "Zone:2. ";
                RepositoryManager.dsi.Merge(NewAppDS);
                RepositoryManager.RemoveAllDeletedRows();

                Initialize();
            }
            catch (Exception ex)
            {
                using (FileStream fs = File.Create(AppSession.sqlitefileDelete))
                {
                    Byte[] file = new UTF8Encoding(true).GetBytes("sqlite file corrupted.");
                    fs.Write(file, 0, file.Length);
                }

                XtraMessageBox.Show(info + "sqlite file corrupted.");
                throw ex;
            }
        }


        public AppDS RefreshData(DateTime date)
        {
            string TABLE_NAME = "";
            try
            {
                DataSet updates = GetUpdates(date);

                AppDS.LookUpDataTable LookUp = new AppDS.LookUpDataTable();
                LookUp.Merge(updates.Tables[0]);
                AppDS NewAppDS = new AppDS();

                if (LookUp.Rows.Count > 0)
                {
                    NewAppDS.EnforceConstraints = false;
                    NewAppDS.LookUp.Merge(LookUp);
                    for (int i = 0; i < LookUp.Rows.Count; i++)
                    {
                        TABLE_NAME = LookUp.Rows[i]["TABLE_NAME"].ToString();
                        NewAppDS.Tables[TABLE_NAME].Merge(updates.Tables[i + 1]);
                    }
                }

                NewAppDS.Tables["Settings"].Merge(updates.Tables[LookUp.Rows.Count + 1]);

                return NewAppDS;
            }
            catch (Exception ex)
            {
                throw new KeyNotFoundException(TABLE_NAME);
            }
        }



        public RepositoryItem this[Rp repository]
        {
            get
            {
                return Get(repository);
            }
        }

        public static void RemoveAllDeletedRows()
        {
            RemoveDeletedRows(-1);
            LastDeletedRowsId = GetLastLocalDeletedRowsId();
        }

        private static int GetLastLocalDeletedRowsId()
        {
            if (dsi.DeletedRows.Count > 0)
                return (int)dsi.DeletedRows.Compute("MAX(DeletedRowsId)", "");
            else
                return -1;
        }
        public static int LastDeletedRowsId = -1;
        public static void RemoveDeletedRows()
        {

            int CurrentDeletedRowsId = GetLastLocalDeletedRowsId();
            if (LastDeletedRowsId != CurrentDeletedRowsId)
            {
                RemoveDeletedRows(LastDeletedRowsId);
                LastDeletedRowsId = CurrentDeletedRowsId;
            }
        }

        public static void RemoveDeletedRows(int LastDeletedRowsId)
        {
            //todo: Conforder için silinen repositoryler 
            DataTable dtDeleted = AppSession.DataManager.ExecuteDataTable("GetDeletedRows", LastDeletedRowsId);
            foreach (DataRow drDeleted in dtDeleted.Rows)
            {
                try
                {
                    string tableName = drDeleted["TABLE_NAME"] as string;
                    string ids = (string)drDeleted["Ids"];

                    DataTable riTable = RepositoryManager.dsi.Tables[tableName];
                    DataRow[] drsToDelete = riTable.Select(string.Format("{0} in ({1})", riTable.PrimaryKey[0].ColumnName, ids));
                    foreach (DataRow drToDelete in drsToDelete)
                    {
                        riTable.Rows.Remove(drToDelete);
                    }
                }
                catch (Exception)
                {

                }

            }
        }

        public static AppDS dsi = new AppDS();

        static Dictionary<int, RepositoryItem> repositories = new Dictionary<int, RepositoryItem>();

        public static RepositoryItem Get(int LookUpId)
        {
            RepositoryItem ri;
            repositories.TryGetValue(LookUpId, out ri);
            return ri;
        }

        public static RepositoryItem Get(Rp repository)
        {
            return repositories[(int)repository];
        }

        DataSet GetUpdates(DateTime LastModified_KA)
        {
            using (SqlConnection sqlConn = new SqlConnection(AppSession.DataManager.ConnectionString))
            {
                SqlCommand sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandText = "GetUpdatesforCustomer2";
                sqlCmd.CommandTimeout = 90;
                sqlCmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] parameters = new SqlParameter[3];
                parameters[0] = new SqlParameter("@UserId", SqlDbType.Int);
                parameters[0].Value = AppSession.UserInfo.UserId;
                parameters[1] = new SqlParameter("@LastModified", SqlDbType.DateTime);
                parameters[1].Value = GetLastModified();
                parameters[2] = new SqlParameter("@LastModified_KA", SqlDbType.DateTime);
                parameters[2].Value = LastModified_KA;

                sqlCmd.Parameters.AddRange(parameters);

                try
                {
                    DataSet ds = new DataSet();
                    DbDataAdapter sqlda = AppSession.DataManager.Database.GetDataAdapter();
                    sqlda.SelectCommand = sqlCmd;
                    sqlda.Fill(ds);
                    return ds;
                }
                catch (Exception Ex)
                {
                    throw (Ex);
                }
            }
        }


        public static DateTime GetLastModified()
        {
            DateTime LastModified = new DateTime(2000, 1, 1);
            foreach (AppDS.LookUpRow lu in RepositoryManager.dsi.LookUp)
                if (lu.LastModified > LastModified)
                    LastModified = lu.LastModified;
            return LastModified;
        }
        DataSet ExecQueryTV(string SP, params object[] p)
        {
            using (SqlConnection sqlConn = new SqlConnection(AppSession.DataManager.ConnectionString))
            {
                sqlConn.Open();
                SqlCommand sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandText = SP;
                sqlCmd.CommandTimeout = 90;
                sqlCmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@LastModified2", SqlDbType.DateTime);
                parameters[0].Value = /*new DateTime(2000, 1, 1)*/ p[0];
                parameters[1] = new SqlParameter("@UserId", SqlDbType.Int);
                parameters[1].Value = p[1];
                sqlCmd.Parameters.AddRange(parameters);

                try
                {
                    DataSet ds = new DataSet();
                    DbDataAdapter sqlda = AppSession.DataManager.Database.GetDataAdapter();
                    sqlda.SelectCommand = sqlCmd;
                    sqlda.Fill(ds);
                    return ds;
                }
                catch (Exception Ex)
                {
                    throw (Ex);
                }
            }
        }


        public static RepositoryItemPictureEdit GetPhotoRI()
        {
            RepositoryItemPictureEdit riPhoto = new RepositoryItemPictureEdit();
            riPhoto.SizeMode = PictureSizeMode.Zoom;
            riPhoto.NullText = " ";
            riPhoto.Padding = new Padding(-1);
            return riPhoto;
        }

        public void Initialize()
        {
            // İç içe geçmiş sütunlarda, sırasız gidebilmek için önce tüm RI ler ekleniyor.
            // Sonra içleri güncelleniyor.

            foreach (AppDS.LookUpRow drLookup in dsi.LookUp.Select("TABLE_NAME <> 'Joker'"))
            {
                DataTable dt = dsi.Tables[drLookup.TABLE_NAME];
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, drLookup.DisplayMember, drLookup.ValueMember);
                repositories.Add(drLookup.LookUpId, ri);
            }

            foreach (AppDS.LookUpRow drLookup in dsi.LookUp.Select("TABLE_NAME <> 'Joker'"))
            {
                DataTable dt = dsi.Tables[drLookup.TABLE_NAME];

                RepositoryItemGridLookUpEdit ri = (RepositoryItemGridLookUpEdit)repositories[drLookup.LookUpId];

                ri.View.BeginUpdate();

                ri.PopupFilterMode = (drLookup.TABLE_NAME == "V_Kumas" ? DevExpress.XtraEditors.PopupFilterMode.Default : AppSession.Settings.PopupFilterMode);  //Default, Contains, StartsWith

                AppDS.LookUpColumnDataTable dtCols = new AppDS.LookUpColumnDataTable();
                dtCols.Merge(AppSession.DataManager.ExecuteDataTable("GetLookUps1"));

                foreach (AppDS.LookUpColumnRow lkCol in dtCols.Select("LookUpId =" + drLookup.LookUpId))
                {
                    GridColumn gridCol = ri.View.Columns[lkCol.FieldName];


                    if (lkCol.FieldName == drLookup.TABLE_NAME + "Id")
                        gridCol.AppearanceCell.BackColor = System.Drawing.Color.LightYellow;

                    switch (lkCol.FieldName)
                    {
                        case "isPreferred":
                        case "ModifiedOn":
                        case "Deleted":
                        case "UserId":
                        case "TS":
                        case "Hue":
                        case "DColor":
                        case "nFirmaId":
                            gridCol.Visible = false;
                            break;
                        default:

                            if (lkCol.FieldName == "Name")
                                gridCol.Caption = drLookup.TABLE_NAME;
                            else if (lkCol.FieldName == "PhotoMikro")
                            {
                                // "GetGLESablon da yapılıyor";
                            }
                            else
                                gridCol.Caption = lkCol.FieldName;

                            if (!lkCol.IsRepositoryIdNull())
                                gridCol.ColumnEdit = RepositoryManager.Get(lkCol.RepositoryId);

                            break;
                    }
                }

                //if (ri.View.Columns.Contains(ri.View.Columns["Deleted"]))
                //{
                //    foreach (DataRow z in dsi.Tables[drLookup.TABLE_NAME].Select("Deleted=1"))
                //        dsi.Tables[drLookup.TABLE_NAME].Rows.Remove(z);

                //    ri.View.RowStyle += new RowStyleEventHandler(gridview_RowStyle);
                //}

                ri.View.EndUpdate();
            }
        }

        public static RepositoryItemGridLookUpEdit GetGLESablon(DataTable SourceTable, string DisplayMember, string ValueMember)
        {
            RepositoryItemGridLookUpEdit gleSablon = new RepositoryItemGridLookUpEdit();
            gleSablon.PopupFilterMode = AppSession.Settings.PopupFilterMode;
            //search columns via the View's OptionsFind.FindFilterColumns property. In the "StartsWith" or "Contains" mode, the search is performed against a field specified by the RepositoryItemSearchLookUpEdit.DisplayMember property.

            gleSablon.AllowNullInput = DefaultBoolean.True;
            gleSablon.View.OptionsView.ColumnAutoWidth = false;
            gleSablon.View.OptionsView.EnableAppearanceEvenRow = AppSession.Settings.EnableAppearanceEvenRow;
            gleSablon.View.OptionsView.ShowAutoFilterRow = true;
            gleSablon.View.OptionsBehavior.Editable = false;
            gleSablon.View.BestFitMaxRowCount = 50;
            gleSablon.View.OptionsFind.HighlightFindResults = true;
            gleSablon.View.OptionsSelection.MultiSelectMode = GridMultiSelectMode.RowSelect;
            gleSablon.TextEditStyle = TextEditStyles.Standard; // DisableTextEditor;
            gleSablon.NullText = string.Empty;
            gleSablon.ImmediatePopup = true;

            gleSablon.PopupFormMinSize = new Size(500, 500);
            gleSablon.BestFitMode = BestFitMode.BestFit;

            gleSablon.Popup += gleSablon_Popup;
            gleSablon.View.OptionsView.ShowFilterPanelMode = ShowFilterPanelMode.Never;

            gleSablon.DataSource = SourceTable;
            gleSablon.DisplayMember = DisplayMember;

            if (gleSablon.DataSource.ToString() == "ProductCategory")
                gleSablon.DisplayMember = "NameEN";

            gleSablon.ValueMember = ValueMember;

            gleSablon.PopulateViewColumns();

            foreach (GridColumn gc in gleSablon.View.Columns)
            {
                gc.FilterMode = ColumnFilterMode.DisplayText;

                switch (gc.FieldName)
                {
                    case "PhotoMikro":
                    case "Scan":
                        RepositoryItemPictureEdit glePic = GetPhotoRI();

                        gleSablon.View.RowHeight = 28;
                        gleSablon.View.Columns["PhotoMikro"].Width = 28;

                        gc.ColumnEdit = glePic;
                        gc.ToolTip = "Scan";
                        gc.Caption = "S";
                        break;
                }
            }

            return gleSablon;
        }

        public static RepositoryItemGridLookUpEdit riStyle
        {
            get
            {
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dsi.COStyle, "Style", "COStyleId");
                ri.View.Columns["BrandId"].ColumnEdit = RepositoryManager.Get(Rp.Brand);
                ri.View.Columns["BrandId"].Caption = "Brand";
                ri.View.Columns["CustomerId"].Visible = false;
                ri.View.Columns["ModifiedOn"].Visible = false;
                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riTalepTipi
        {
            get
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("TypeId", typeof(int));
                dt.Columns.Add("Name", typeof(string));
                dt.Rows.Add(1, "Production");
                dt.Rows.Add(2, "Sample");
                dt.Rows.Add(4, "Collection");

                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "Name", "TypeId");
                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riCustomer
        {
            get
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("CustomerId", typeof(int));
                dt.Columns.Add("Name", typeof(string));
                dt.Rows.Add(1, "KARBEL");
                dt.Rows.Add(175, "ESPRESSO");
                dt.Rows.Add(75, "WESTBROOK");

                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "Name", "CustomerId");
                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riOrderRef
        {
            get
            {
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dsi.COOrderRef, "OrderRef", "OrderRefId");
                ri.View.Columns["CustomerId"].Visible = false;
                //ri.View.Columns["ModifiedOn"].Visible = false;
                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riState
        {
            get
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("StateId", typeof(char));
                dt.Columns.Add("State", typeof(string));
                dt.Rows.Add('N', "New");
                dt.Rows.Add('C', "Waiting for codes");
                dt.Rows.Add('O', "Waiting for production"); //Sumit edilince kullanılıyor
                dt.Rows.Add('F', "Pre-Submit"); //Finalize edilince kullanılıyor
                dt.Rows.Add('P', "In Production");
                dt.Rows.Add('W', "In Warehouse");
                dt.Rows.Add('S', "Sent");
                dt.Rows.Add('X', "Cancelled");

                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "State", "StateId");
                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riArticleOrderState
        {
            get
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("StateId", typeof(int));
                dt.Columns.Add("State", typeof(string));
                dt.Rows.Add(1, "Open");
                dt.Rows.Add(2, "Closed");
                dt.Rows.Add(3, "Cancelled");
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "State", "StateId");
                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riDagilim
        {
            get
            {
                DataTable dtV = RepositoryManager.dsi.Dagilim.Select("DagilimId in (2,50,49,7,15,16,32,48,36,51,30,56,10,60,61)").CopyToDataTable();
                dtV.RemoveSystemCols();
                dtV.Columns["DagilimId"].ColumnName = "BreakdownTypeId";
                dtV.Columns["Name"].Caption = "Name of the breakdown";

                RepositoryItemGridLookUpEdit ri = GetGLESablon(dtV, "Name", "BreakdownTypeId");
                ri.View.Columns["BreakdownTypeId"].Visible = false;

                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riCODagilim
        {
            get
            {
                DataTable dtV = RepositoryManager.dsi.CODagilim;
                dtV.RemoveSystemCols();
                dtV.Columns["Name"].Caption = "Name of the breakdown";

                RepositoryItemGridLookUpEdit ri = GetGLESablon(dtV, "Name", "CODagilimId");
                ri.View.Columns["CODagilimId"].Visible = false;

                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riBrand
        {
            get
            {
                DataTable dtV = RepositoryManager.dsi.Brand.Select("CustomerId = " + AppSession.UserInfo.CustomerId).CopyToDataTable();
                dtV.RemoveSystemCols();
                dtV.Columns.Remove("CustomerId");

                RepositoryItemGridLookUpEdit ri = GetGLESablon(dtV, "Name", "BrandId");

                return ri;
            }
        }

        public static RepositoryItemGridLookUpEdit riVendor
        {
            get
            {
                DataTable dt = new DataTable();
                dt = AppSession.DataManager.ExecuteDataTableByQuery("select VendorId, Abbr, case Name when 'TR UYGUN' then 'Turkey' when 'UZAK DOĞU' then 'Far East' else Name end Name from Vendor where StokTipiId=1");
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "Name", "VendorId");
                return ri;
            }
        }

        static void gleSablon_Popup(object sender, EventArgs e)
        {
            GridLookUpEdit gle = (GridLookUpEdit)sender;
            RepositoryItemGridLookUpEdit ri = gle.Properties;
            if (ri.View.Columns.Contains(ri.View.Columns["Deleted"]))
                ri.View.ActiveFilterString = "isnull(Deleted,false) = false";
            gle.Properties.View.BestFitColumns();
        }

        //void gridview_RowStyle(object sender, RowStyleEventArgs e)
        //{
        //    object deletedValue = (((GridView)sender).GetRowCellValue(e.RowHandle, "Deleted"));
        //    if (deletedValue != null && deletedValue != DBNull.Value && (bool)deletedValue)
        //        e.Appearance.ForeColor = Color.LightGray;
        //}

        public static RepositoryItem riPODurum
        {
            get
            {
                DataTable dt = new DataTable();
                dt = AppSession.DataManager.ExecuteDataTableByQuery("select PODurumId,case PODurumId when 1 then 'New' when 2 then 'Sheet Created' when 3 then 'Sheed Confirmed' when 4 then 'Order Created' when 5 then 'Order Sent' when 6 then 'Partially Arrived' when 7 then 'Arrived' when 8 then 'Cancelled' when 9 then 'Deleted' else  '???' end Name from PODurum");
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "Name", "PODurumId");
                return ri;
            }
        }

        public static RepositoryItem riAyrim
        {
            get
            {
                DataTable dt = new DataTable();
                dt = AppSession.DataManager.ExecuteDataTableByQuery("select AyrimId, Reference from Ayrim where BrandId in (Select BrandId from Brand where CustomerId in (1,75,175))");
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "Reference", "AyrimId");
                return ri;
            }
        }

        public static RepositoryItem riCurrency
        {
            get
            {
                DataTable dt = new DataTable();
                dt = AppSession.DataManager.ExecuteDataTableByQuery("select CurrencyId,Abbr,Name from Currency");
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "Abbr", "CurrencyId");
                return ri;
            }
        }

        public static RepositoryItem riPaymentMethod
        {
            get
            {
                DataTable dt = new DataTable();
                dt = AppSession.DataManager.ExecuteDataTableByQuery("select PaymentMethodId, replace(replace(replace(abbr,'ÇEK','Check') , 'SENET' , 'Deed') , 'PEŞİN' , 'Cash')  Name  from paymentmethoda");
                RepositoryItemGridLookUpEdit ri = GetGLESablon(dt, "Name", "PaymentMethodId");
                return ri;
            }
        }
    }
}